using ClosedXML.Excel;
using System.Data;
using System.IO;

namespace RBAC.Write.Api.Application.Handler.ERP.MaterialFile
{
    /// <summary>
    /// 导入模板生成器
    /// </summary>
    public static class ImportTemplateGenerator
    {
        /// <summary>
        /// 生成物料分类导入模板
        /// </summary>
        /// <returns>Excel模板字节数组</returns>
        public static byte[] GenerateMaterTypeTemplate()
        {
            using (var workbook = new XLWorkbook())
            {
                var worksheet = workbook.Worksheets.Add("物料分类导入模板");

                // 创建表头
                var headers = new[]
                {
                    new { Name = "分类编码", Description = "必填，唯一标识，建议使用字母+数字组合", Required = "是" },
                    new { Name = "分类名称", Description = "必填，物料分类的名称", Required = "是" },
                    new { Name = "计量单位", Description = "必填，如：个、件、箱、kg等", Required = "是" },
                    new { Name = "状态", Description = "选填，启用/禁用，默认为启用", Required = "否" },
                    new { Name = "流水号长度", Description = "选填，默认为4位数字", Required = "否" },
                    new { Name = "供应方式", Description = "选填，采购/加工/委外/装配/生产/无", Required = "否" },
                    new { Name = "上级分类编码", Description = "选填，上级分类的编码，顶级分类留空", Required = "否" }
                };

                // 添加示例数据
                var exampleData = new[]
                {
                    new { TypeCode = "MAT001", TypeName = "原材料", Unit = "kg", Status = "启用", Lenght = "4", SupplyMode = "采购", ParentCode = "" },
                    new { TypeCode = "MAT002", TypeName = "半成品", Unit = "件", Status = "启用", Lenght = "4", SupplyMode = "生产", ParentCode = "MAT001" },
                    new { TypeCode = "MAT003", TypeName = "成品", Unit = "箱", Status = "启用", Lenght = "6", SupplyMode = "装配", ParentCode = "MAT002" }
                };

                // 设置表头
                for (int i = 0; i < headers.Length; i++)
                {
                    worksheet.Cell(1, i + 1).Value = headers[i].Name;
                    worksheet.Cell(2, i + 1).Value = headers[i].Description;
                    worksheet.Cell(3, i + 1).Value = headers[i].Required;
                }

                // 设置示例数据
                for (int i = 0; i < exampleData.Length; i++)
                {
                    worksheet.Cell(i + 5, 1).Value = exampleData[i].TypeCode;
                    worksheet.Cell(i + 5, 2).Value = exampleData[i].TypeName;
                    worksheet.Cell(i + 5, 3).Value = exampleData[i].Unit;
                    worksheet.Cell(i + 5, 4).Value = exampleData[i].Status;
                    worksheet.Cell(i + 5, 5).Value = exampleData[i].Lenght;
                    worksheet.Cell(i + 5, 6).Value = exampleData[i].SupplyMode;
                    worksheet.Cell(i + 5, 7).Value = exampleData[i].ParentCode;
                }

                // 设置样式
                // 表头样式
                var headerRange = worksheet.Range("A1:G1");
                headerRange.Style.Font.Bold = true;
                headerRange.Style.Font.FontColor = XLColor.White;
                headerRange.Style.Fill.BackgroundColor = XLColor.CornflowerBlue;
                headerRange.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;

                // 描述行样式
                var descRange = worksheet.Range("A2:G2");
                descRange.Style.Font.FontColor = XLColor.Gray;
                descRange.Style.Font.Italic = true;
                descRange.Style.Fill.BackgroundColor = XLColor.LightGray;

                // 必填行样式
                var requiredRange = worksheet.Range("A3:G3");
                requiredRange.Style.Font.Bold = true;
                requiredRange.Style.Font.FontColor = XLColor.Red;
                requiredRange.Style.Fill.BackgroundColor = XLColor.LightYellow;

                // 示例数据标题
                worksheet.Cell(4, 1).Value = "示例数据（可删除）";
                worksheet.Range("A4:G4").Style.Font.Bold = true;
                worksheet.Range("A4:G4").Style.Font.FontColor = XLColor.Green;

                // 自动调整列宽
                worksheet.Columns().AdjustToContents();

                // 设置边框
                worksheet.RangeUsed().Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
                worksheet.RangeUsed().Style.Border.InsideBorder = XLBorderStyleValues.Thin;

                // 设置列宽
                worksheet.Column(1).Width = 15; // 分类编码
                worksheet.Column(2).Width = 20; // 分类名称
                worksheet.Column(3).Width = 12; // 计量单位
                worksheet.Column(4).Width = 10; // 状态
                worksheet.Column(5).Width = 12; // 流水号长度
                worksheet.Column(6).Width = 15; // 供应方式
                worksheet.Column(7).Width = 15; // 上级分类编码

                // 添加数据验证
                // 状态列数据验证
                var statusRange = worksheet.Range("D5:D1000");
                var statusValidation = statusRange.CreateDataValidation();
                statusValidation.List("启用,禁用", true);

                // 供应方式列数据验证
                var supplyRange = worksheet.Range("F5:F1000");
                var supplyValidation = supplyRange.CreateDataValidation();
                supplyValidation.List("采购,加工,委外,装配,生产,无", true);

                // 流水号长度数据验证
                var lengthRange = worksheet.Range("E5:E1000");
                var lengthValidation = lengthRange.CreateDataValidation();
                lengthValidation.WholeNumber.EqualOrGreaterThan(1);
                lengthValidation.WholeNumber.EqualOrLessThan(10);

                using (var memoryStream = new MemoryStream())
                {
                    workbook.SaveAs(memoryStream);
                    return memoryStream.ToArray();
                }
            }
        }
    }
}